/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Alex
 */
public class studentId {

    Connection con;

    public studentId() {
        con = Connect.getConnection();
    }

    public ArrayList getSearchmarks(int stuID) {
        ArrayList list = new ArrayList();
        try {//
            String sql = "select stuId, stuName, suName, maMark from Students inner join Marks on Students.stuId=Marks.maStudent inner join Subjects on Marks.maSubject=Subjects.suId where stuId=?";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, "" + stuID);
            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
                ExamMark ls = new ExamMark();
                ls.setStuId(rs.getInt(1));
                ls.setStuName(rs.getString(2));
                ls.setSuName(rs.getString(3));
                ls.setMaMark(rs.getFloat(4));
                list.add(ls);
            }
            rs.close();
            con.close();
            return list;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public ArrayList getSearchMulti(String cuName,String suSemester, String stuName, String claName)
    {
        ArrayList ar = new ArrayList();
        try {
            String sql = "select stuName, cuName, suSemester, claName"+
                    "from Curriculums join Subjects on CurriculumS.cuID = Subjects.suCurri"+
                    "join Classes  on CLasses.claSubject = Subjects.suId"+
                    "join Students on Students.stuClass=Classes.claId"+"where cuName=? or suSemester=? or stuName=? or claName=?";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, "" + cuName);
            stm.setString(2, "" + suSemester);
            stm.setString(3, "" + stuName);
            stm.setString(4, "" + claName);
            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
                SearchMultiBean smb = new SearchMultiBean(cuName, stuName, suSemester, claName);
                smb.setCuName(rs.getString(cuName));
                smb.setSuSemester(rs.getString(suSemester));
                smb.setStuName(rs.getString(stuName));
                smb.setClaName(rs.getString(claName));
                ar.add(rs);
            }
            rs.close();
            con.close();
            return ar;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

}
